package com.ims.repository;

import com.b2c.common.third.thymeleaf.PagingResponse;
import com.ims.entity.LiveDataReportEntity;
import com.ims.entity.vo.StatisticsDataVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class LiveDataReportRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public PagingResponse<LiveDataReportEntity> getList(int pageIndex, int pageSize, Integer mediaAccountId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS a.*,b.name as accountName ");
        sb.append(" FROM live_data_report a LEFT JOIN media_account b ON b.id=a.accountId");
        sb.append(" where 1=1 ");
        List<Object> params = new ArrayList<>();


        if (mediaAccountId!=null && mediaAccountId > 0) {
            sb.append(" AND accountId =? ");
            params.add(mediaAccountId);
        }

        sb.append(" ORDER BY id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<>(LiveDataReportEntity.class),params.toArray(new Object[params.size()]));

        int total = jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);

        return new PagingResponse<>(pageIndex, pageSize, total, list);
    }


    /**
     * 添加数据
     * @param entity
     * @return
     */
    public Integer add(LiveDataReportEntity entity){
        /*************插入media_data_report**************/
        String sql = "INSERT INTO live_data_report (accountId,liveDate,startTime,endTime,viewCount,newUser,commentCount,source1,source2,source3,source4,source5,orderCount,orderAmount,summary,improvement,createTime,createUserId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setInt(1, entity.getAccountId());
                ps.setObject(2, entity.getLiveDate());
                ps.setObject(3, entity.getStartTime());
                ps.setObject(4, entity.getEndTime());
                ps.setInt(5, entity.getViewCount());
                ps.setInt(6, entity.getNewUser());
                ps.setInt(7, entity.getCommentCount());
                ps.setInt(8, entity.getSource1());
                ps.setInt(9, entity.getSource2());
                ps.setInt(10, entity.getSource3());
                ps.setInt(11, entity.getSource4());
                ps.setInt(12, entity.getSource5());
                ps.setInt(13, entity.getOrderCount());
                ps.setBigDecimal(14, entity.getOrderAmount());
                ps.setString(15, entity.getSummary());
                ps.setString(16, entity.getImprovement());
                ps.setLong(17, System.currentTimeMillis() / 1000);
                ps.setInt(18, entity.getCreateUserId());
                return ps;
            }
        }, keyHolder);

        Integer reportId = keyHolder.getKey().intValue();
        return reportId;
    }

    public LiveDataReportEntity getById(Integer id) {
        try {
            return jdbcTemplate.queryForObject("SELECT a.*,b.name as accountName FROM live_data_report  a LEFT JOIN media_account b ON b.id=a.accountId WHERE a.id=?",new BeanPropertyRowMapper<>(LiveDataReportEntity.class),id);

        }catch (Exception e){
            return null;
        }
    }

    public void edit(LiveDataReportEntity entity) {
        /*************插入media_data_report**************/
        String sql = "UPDATE live_data_report SET liveDate=?,startTime=?,endTime=?,viewCount=?,newUser=?,commentCount=?,source1=?,source2=?,source3=?,source4=?,source5=?,orderCount=?,orderAmount=?,summary=?,improvement=?,updateTime=? WHERE id=?";
        jdbcTemplate.update(sql, entity.getLiveDate(), entity.getStartTime(), entity.getEndTime(), entity.getViewCount()
                , entity.getNewUser(), entity.getCommentCount(), entity.getSource1(), entity.getSource2(), entity.getSource3(), entity.getSource4(), entity.getSource5(),
                entity.getOrderCount(), entity.getOrderAmount(), entity.getSummary(), entity.getImprovement(), System.currentTimeMillis() / 1000,
                entity.getId()
        );

    }
    /**
     * 媒体数据统计
     * @param mediaAccountId
     * @return
     */
    public StatisticsDataVo getMediaData(Integer mediaAccountId) {
        StatisticsDataVo data=new StatisticsDataVo();
        data.setLineName(jdbcTemplate.query("SELECT a.liveDate as value FROM live_data_report a where a.accountId=? order by a.id desc limit 9",new BeanPropertyRowMapper<>(StatisticsDataVo.ShowValue.class),mediaAccountId));
        data.setLineValue(jdbcTemplate.query("SELECT a.viewCount as value FROM live_data_report a where a.accountId=? order by a.id desc limit 9",new BeanPropertyRowMapper<>(StatisticsDataVo.ShowValue.class),mediaAccountId));
        data.setUserCount(jdbcTemplate.queryForObject("select IFNULL(SUM(userCount),0)  from  media_data_report where accountId=? ",Integer.class,mediaAccountId));
        data.setZanCount(jdbcTemplate.queryForObject("select IFNULL(SUM(i.zanCount),0)  from  media_data_report_item i left join media_data_report r on i.reportId= r.id where r.accountId=? ",Integer.class,mediaAccountId));
        data.setBarName(jdbcTemplate.query("select DATE_FORMAT(i.publishDate,'%Y-%m') as value  from  media_data_report_item i left join media_data_report r on i.reportId= r.id where r.accountId=? GROUP BY DATE_FORMAT(i.publishDate,'%Y-%m') ORDER BY i.publishDate LIMIT 12 ",new BeanPropertyRowMapper<>(StatisticsDataVo.ShowValue.class),mediaAccountId));
        data.setBarValue(jdbcTemplate.query("select  SUM(i.zanCount) as value  from  media_data_report_item i left join media_data_report r on i.reportId= r.id where r.accountId=? GROUP BY DATE_FORMAT(i.publishDate,'%Y-%m') ORDER BY i.publishDate LIMIT 12 ",new BeanPropertyRowMapper<>(StatisticsDataVo.ShowValue.class),mediaAccountId));
        return data;
    }

    /**
     * 直播数据统计
     * @param accountId
     * @return
     */
    public StatisticsDataVo getLiveData(Integer accountId) {
        StatisticsDataVo data=new StatisticsDataVo();
        data.setLineName(jdbcTemplate.query("SELECT a.liveDate as value FROM live_data_report a where a.accountId=? order by a.id desc limit 9",new BeanPropertyRowMapper<>(StatisticsDataVo.ShowValue.class),accountId));
        data.setLineValue(jdbcTemplate.query("SELECT IFNULL(SUM(a.orderCount),0) as value FROM live_data_report a where a.accountId=? order by a.id desc limit 9",new BeanPropertyRowMapper<>(StatisticsDataVo.ShowValue.class),accountId));
        data.setUserCount(jdbcTemplate.queryForObject("select userCount  from  media_account where id=? ",Integer.class,accountId));
        data.setZanCount(jdbcTemplate.queryForObject("select IFNULL(SUM(i.zanCount),0)  from  media_data_report_item i left join media_data_report r on i.reportId= r.id where r.accountId=? ",Integer.class,accountId));
        data.setBarName(jdbcTemplate.query("select DATE_FORMAT(i.liveDate,'%Y-%m') as value  from  live_data_report i  where i.accountId=? GROUP BY DATE_FORMAT(i.liveDate,'%Y-%m') ORDER BY i.liveDate LIMIT 12 ",new BeanPropertyRowMapper<>(StatisticsDataVo.ShowValue.class),accountId));
        data.setBarValue(jdbcTemplate.query("select  SUM(i.viewCount) as value  from  live_data_report i  where i.accountId=? GROUP BY DATE_FORMAT(i.liveDate,'%Y-%m') ORDER BY i.liveDate LIMIT 12 ",new BeanPropertyRowMapper<>(StatisticsDataVo.ShowValue.class),accountId));
        return data;
    }
}
